﻿/*
 * Jonathan
 * Created by: Jonathan Montiverdi
 * Date: 7/10/2012
 * License: http://jonathan.codeplex.com/license
 */
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading;
using System.Windows.Input;
using System.Xml;

using Jonathan.Data;
using Jonathan.DataAccess.MediaWiki;
using Jonathan.DataAccess.Sqlite;
using Monti.MVVM;
using Monti.MVVM.Services;

namespace Jonathan.Logic
{
   /// <summary>
   /// Description of WikiConverter.
   /// </summary>
   public class WikiConverterViewModel : DialogViewModelBase, IDisposable
   {
      #region Fields
      
      private bool m_CopyAll;
      private bool m_CanCopy;
      private bool m_CanClose;
      private string m_Status;
      private IOptions m_Options;
      private string m_TimeStamp;
      private RelayCommand m_CmdAction;
      private SQLiteConnection m_DbMain;
      private readonly string m_ConvertDir;
      private readonly WikiParser m_WikiParser;
      private readonly List <SQLiteTransaction> m_Transactions;
      private readonly Dictionary <string, SQLiteConnection> m_Dbs;
      private readonly Dictionary <string, DateTime> m_LastModifiedDates;
      
      private readonly Regex m_RegExBook = new Regex (@"(book\s?=\s?)(.+)");
      private readonly Regex m_RegExChap = new Regex (@"(chapter\s?=\s?)(.+)");
      private readonly Regex m_RegExVerse = new Regex (@"(verse\s?=\s?)(.+)");
      private readonly Regex m_RegExPass = new Regex (@"(passage\s?=\s?)(.+)");
      private readonly Regex m_RegExHead = new Regex (@"([H|h]eader\s?=\s?)(.+)");
      private readonly Regex m_RegExFoot = new Regex (@"([F|f]ooter\s?=\s?)(.+)");
      private readonly Regex m_RegExKjv = new Regex (@"(KJV\s?=\s?)(.+)");
      private readonly Regex m_RegExBbe = new Regex (@"(BBE\s?=\s?)(.+)");
      private readonly Regex m_RegExDr = new Regex (@"(D-R\s?=\s?)(.+)");
      private readonly Regex m_RegExWnt = new Regex (@"(WNT\s?=\s?)(.+)");
      private readonly Regex m_RegExDef = new Regex (@"{{Definition\|(.+)\|(.+)}}");

      private readonly Regex m_RegExAmazonBook = new Regex (@"<amazonbook>(\d+)</amazonbook>");
      private readonly Regex m_RegExHasSubBooks = new Regex (@"\[\[HasSubBooks::(\w+)\|\s]]");
      private readonly Regex m_RegExChapterTitle = new Regex (@"\[\[ChapterTitle::([\w\s\d]+)\|\s]]");
      private readonly Regex m_RegExIgnoreContentFolders = new Regex (@"\[\[IgnoreContentFolders::(\w+)\|\s]]");
      
      #endregion
      
      #region Setup
      
      /// <summary>
      /// Initilializes a new wiki converter.
      /// </summary>
      public WikiConverterViewModel ()
      {
         this.CanClose = true;
         this.m_CopyAll = true;
         this.m_WikiParser = new WikiParser ();
         this.m_Transactions = new List <SQLiteTransaction> ();
         this.m_ConvertDir = Common.ApplicationPath + "Conversion\\";
         this.m_Dbs = new Dictionary <string, SQLiteConnection> ();
         this.UnknownPages = new ObservableCollection <string> ();
         this.m_LastModifiedDates = new Dictionary <string, DateTime> ();
         this.ConvertedBooks = new ObservableCollection <CheckedListItem> ();
      }
      
      #endregion
      
      #region Properties

      /// <summary>
      /// Gets the list of unknown pages.
      /// </summary>
      public ObservableCollection <string> UnknownPages { get; private set; }

      /// <summary>
      /// Gets the list of converted books.
      /// </summary>
      public ObservableCollection <CheckedListItem> ConvertedBooks { get; private set; }
      
      /// <summary>
      /// Gets the options of the application.
      /// </summary>
      public IOptions Options { get { return this.m_Options ?? (this.m_Options = ServiceManager.Resolve <IOptions> ()); } }
      
      /// <summary>
      /// Gets the command for all the actions
      /// </summary>
      public ICommand ActionCommand
      {
         get { return this.m_CmdAction ?? (this.m_CmdAction = new RelayCommand (this.OnAction, this.CanDoAction)); }
      }
      
      /// <summary>
      /// Gets or Sets the current status.
      /// </summary>
      public string Status
      {
         get { return this.m_Status; }
         set
         {
            if (this.m_Status != value)
            {
               this.m_Status = value;
               this.RaisePropertyChanged ("Status");
            }
         }
      }
      
      /// <summary>
      /// Gets or Sets if the updater can copy all databases or not.
      /// </summary>
      public bool CopyAll
      {
         get { return this.m_CopyAll; }
         set
         {
            if (this.m_CopyAll != value)
            {
               this.m_CopyAll = value;
               foreach (var book in this.ConvertedBooks)
                  book.IsChecked = this.m_CopyAll;
               this.RaisePropertyChanged ("CopyAll");
            }
         }
      }
      
      /// <summary>
      /// Gets or Sets if the updater can copy converted databases or not.
      /// </summary>
      public bool CanCopy
      {
         get { return this.m_CanCopy; }
         set
         {
            if (this.m_CanCopy != value)
            {
               this.m_CanCopy = value;
               this.RaisePropertyChanged ("CanCopy");
            }
         }
      }
      
      /// <summary>
      /// Gets or Sets if the updater can close or not.
      /// </summary>
      public bool CanClose
      {
         get { return this.m_CanClose; }
         set
         {
            if (this.m_CanClose != value)
            {
               this.m_CanClose = value;
               this.RaisePropertyChanged ("CanClose");
            }
         }
      }
      
      #endregion
      
      #region Methods
      
      /// <summary>
      /// Converts the wiki into sqlite databases.
      /// </summary>
      public void Convert ()
      {
         this.CanClose = false;
         var t = new Thread (this.Setup);
         t.Start ();
      }
      
      /// <summary>
      /// Sets sqlite databases and downloads page dump.
      /// </summary>
      private void Setup ()
      {
         /*var pageDump = this.m_ConvertDir + "pagedump.xml";
         this.Status = "Setting up directory.";
         if (Directory.Exists (this.m_ConvertDir))
            Directory.Delete (this.m_ConvertDir, true);
         Thread.Sleep (2000);
         Directory.CreateDirectory (this.m_ConvertDir);*/
         this.CreateMainDb ();
         
         this.Status = "Downloading page dump.";
         /*var download = Downloader.AddToQueue ("pagedump.xml", pageDump);
         download.Completed += this.PageDumpCompleted;*/
         this.PageDumpCompleted (null, null);
      }
      
      /// <summary>
      /// Begins the conversion.
      /// </summary>
      private void PageDumpCompleted (object sender, EventArgs e)
      {
         //var download = sender as Download;
         //download.Completed -= this.PageDumpCompleted;
         
         var pageDump = this.m_ConvertDir + "pagedump.xml";
         this.Status = "Converting page dump.";
         using (var reader = new XmlTextReader (pageDump))
         {
            var title = string.Empty;
            while (reader.Read ())
            {
               if (reader.NodeType == XmlNodeType.Element &&
                   reader.Name == "title")
               {
                  title = reader.ReadElementContentAsString ().Trim ();
                  this.Status = string.Format ("Converting {0}.", title);
               }
               if (reader.NodeType == XmlNodeType.Element &&
                   reader.Name == "timestamp")
               {
                  this.m_TimeStamp = reader.ReadElementContentAsString ().Trim ();
               }
               if (reader.NodeType == XmlNodeType.Element &&
                   reader.Name == "text")
               {
                  var content = reader.ReadElementContentAsString ().Trim ();
                  if (content.Contains ("{{Excerpt"))
                  {
                     var mBook = this.m_RegExBook.Match (content);
                     var mChap = this.m_RegExChap.Match (content);
                     var mVerse = this.m_RegExVerse.Match (content);
                     var mPass = this.m_RegExPass.Match (content);
                     var mHead = this.m_RegExHead.Match (content);
                     var mFoot = this.m_RegExFoot.Match (content);
                     var mKjv = this.m_RegExKjv.Match (content);
                     var mBbe = this.m_RegExBbe.Match (content);
                     var mDr = this.m_RegExDr.Match (content);
                     var mWnt = this.m_RegExWnt.Match (content);
                     
                     if (mPass.Success)
                     {
                        this.AddExcerpt (mBook.Groups [2].Value, null, mChap.Groups [2].Value,
                                         mVerse.Groups [2].Value, mPass.Groups [2].Value, mHead, mFoot);
                     }
                     else
                     {
                        if (mKjv.Success)
                           this.AddExcerpt ("King James Bible", mBook.Groups [2].Value, mChap.Groups [2].Value,
                                            mVerse.Groups [2].Value, mKjv.Groups [2].Value, mHead, mFoot, true);
                        if (mBbe.Success)
                           this.AddExcerpt ("Bible in Basic English", mBook.Groups [2].Value, mChap.Groups [2].Value,
                                            mVerse.Groups [2].Value, mBbe.Groups [2].Value, mHead, mFoot, true);
                        if (mDr.Success)
                           this.AddExcerpt ("Douay-Rheims Bible", mBook.Groups [2].Value, mChap.Groups [2].Value,
                                            mVerse.Groups [2].Value, mDr.Groups [2].Value, mHead, mFoot, true);
                        if (mWnt.Success)
                           this.AddExcerpt ("Weymouth New Testament", mBook.Groups [2].Value, mChap.Groups [2].Value,
                                            mVerse.Groups [2].Value, mWnt.Groups [2].Value, mHead, mFoot, true);
                     }
                  }
                  else if (title == "Main Page" ||
                           title.StartsWith ("Property") ||
                           title.StartsWith ("Category") ||
                           title.StartsWith ("Template") ||
                           title.StartsWith ("MediaWiki"))
                  {
                     this.AddToMain (title, content, "Pages", false);
                  }
                  else if (content.Contains ("[[Category:Definition]]"))
                  {
                     this.AddDefinition (title, content);
                  }
                  else if (content.Contains ("[[Category:Book]]"))
                  {
                     this.ParseBook (title, content);
                  }
                  else if (content.Contains ("{{Chapter"))
                  {
                     
                  }
                  else if (content.Contains ("{{Article"))
                  {
                     
                  }
                  else
                  {
                     MainViewModel.GuiDispatcher.Invoke (new Action <string> (page =>this.UnknownPages.Add (page)), title);
                  }
               }
            }
         }
         
         this.Status = "Setting modified dates.";
         using (var command = this.m_DbMain.CreateCommand ())
         {
            command.Parameters.Add (new SQLiteParameter ("@lastmodified"));
            command.Parameters.Add (new SQLiteParameter ("@bookName"));
            foreach (var kvp in this.m_LastModifiedDates)
            {
               command.CommandText = "UPDATE 'Books' SET LastModified=@lastmodified WHERE Name=@bookName;";
               command.Parameters [0].Value = kvp.Value.ToString (CultureInfo.InvariantCulture);
               command.Parameters [1].Value = kvp.Key;
               command.ExecuteNonQuery ();
               
               var db = this.m_Dbs [kvp.Key];
               using (var c = db.CreateCommand ())
               {
                  c.CommandText = "INSERT INTO 'BookInfo' (PropertyName, PropertyValue) VALUES (@pname, @pvalue);";
                  c.Parameters.AddWithValue("@pname", "LastModified");
                  c.Parameters.AddWithValue("@pvalue", kvp.Value.ToString (CultureInfo.InvariantCulture));
                  c.ExecuteNonQuery ();
               }
            }
         }
         
         this.Status = "Saving databases.";
         foreach (var transaction in this.m_Transactions)
         {
            transaction.Commit ();
            transaction.Dispose ();
         }
         this.m_LastModifiedDates.Clear ();
         this.m_Transactions.Clear ();
         this.Dispose ();
         this.CanCopy = true;
         this.CanClose = true;
         this.Status = "Done";
         CommandManager.InvalidateRequerySuggested ();
      }

      /// <summary>
      /// Parses the given book content.
      /// </summary>
      /// <param name="title">Title of book.</param>
      /// <param name="content">Content of book</param>
      void ParseBook (string title, string content)
      {
         var item = 0;
         var lastIndent = 1;
         var ignore = false;
         var isAbout = false;
         var isContents = false;
         var contents = "<ul><li><a href=\"About\">About</a></li>" + Environment.NewLine;
         var about = string.Empty;
         var info = "<p>";
         var author = string.Empty;
         var hasContents = false;
         var hasSubBooks = false;
         var ignoreContentFolder = true;
         var chapterTitle = string.Empty;
         var isBible = content.Contains ("[[Category:Bible]]");
         var authorMatch = this.m_WikiParser.RegEx_Author.Match (content);
         var subBookMatch = this.m_RegExHasSubBooks.Match (content);
         var chapTitleMatch = this.m_RegExChapterTitle.Match (content);
         var ignoreFolderMatch = this.m_RegExIgnoreContentFolders.Match (content);
         if (authorMatch.Success) author = authorMatch.Groups [2].Value;
         if (subBookMatch.Success) hasSubBooks = subBookMatch.Groups [1].Value.ToBool ();
         if (chapTitleMatch.Success) chapterTitle = chapTitleMatch.Groups [1].Value.Trim ();
         if (ignoreFolderMatch.Success) ignoreContentFolder = ignoreFolderMatch.Groups [1].Value.ToBool ();
         var subBookOrder = new List <string> ();
         var chapterOrder = new List <string> ();
         
         using (var contentReader = new StringReader(content))
         {
            while (contentReader.Peek() != -1)
            {
               var line = contentReader.ReadLine();
               if (!string.IsNullOrEmpty(line))
               {
                  if (line.Contains("{{#tree:"))
                  {
                     hasContents = true;
                     isContents = true;
                  }
                  else if (line.Contains("{{About|"))
                  {
                     isAbout = true;
                     if (!info.Trim ().EndsWith ("<p>"))
                        about += "<p>";
                     info += "__ABOUT__" + Environment.NewLine;
                  }
                  else if (line.Contains ("{{Article"))
                  {
                     ignore = true;
                  }
                  else if (ignore)
                  {
                     if (line.Contains ("}}"))
                        ignore = false;
                  }
                  else if (isContents)
                  {
                     if (line == "}}")
                     {
                        if (lastIndent > 1)
                           contents += "</ul></li>" + Environment.NewLine;
                        isContents = false;
                        continue;
                     }
                     var match = this.m_WikiParser.RegEx_ListItem.Match (line);
                     var indent = match.Groups [1].Length;
                     var linkmatch = this.m_WikiParser.RegEx_WikiLink.Match (match.Groups [3].Value);
                     if (lastIndent > indent)
                           contents += "</ul></li>" + Environment.NewLine;
                     if (!linkmatch.Success)
                     {
                        var id = "item-" + item + "-" + System.Convert.ToString (indent - 1);
                        contents += string.Format ("<li><input type='checkbox' id='{0}' /><label for='{0}'>{1}</label>{2}",
                                                   id, match.Groups [3].Value, Environment.NewLine);
                        contents += "<ul>";
                        if (!ignoreContentFolder)
                           subBookOrder.Add (match.Groups [3].Value);
                        item++;
                     }
                     else
                     {
                        var link = linkmatch.Groups[1].Value;
                        var innerText = link;
                        if (link.Contains ("|"))
                        {
                           var linkSplit = link.Split ('|');
                           link = linkSplit [0];
                           innerText = linkSplit [1];
                        }
                        contents += string.Format ("<li><a href=\"{0}\">{1}</a></li>{2}", link, innerText,
                                                   Environment.NewLine);
                        var ls = link.Split (':');
                        chapterOrder.Add (ls [ls.Length - 1]);
                     }
                     lastIndent = indent;
                  }
                  else if (line.Trim ().StartsWith ("|") ||
                           line.Trim ().StartsWith ("{|"))
                  {
                  }
                  else if (isAbout)
                  {
                     if (line.Trim ().EndsWith ("}}"))
                     {
                        line = line.TrimEnd ('}');
                        isAbout = false;
                     }
                     about += line + Environment.NewLine;
                  }
                  else
                  {
                     info += line + Environment.NewLine;
                  }
               }
               else if (isAbout)
                  about += Environment.NewLine + "<p>";
               else
                  info += Environment.NewLine + "<p>";
            }
         }
         if (!hasContents)
            contents += "<li><a href=\"Read\">Read Book</a></li>" + Environment.NewLine;
         contents += "</ul>";
         
         about = this.UnWiki (about);
         using (var command = this.m_DbMain.CreateCommand ())
         {
            command.CommandText = "UPDATE 'Books' SET About=@about, Author=@author WHERE Name=@bookName;";
            command.Parameters.AddWithValue("@about", about);
            command.Parameters.AddWithValue("@author", author);
            command.Parameters.AddWithValue("@bookName", title);
            command.ExecuteNonQuery ();
         }
         
         var db = this.GetDb (title, isBible);
         using (var command = db.CreateCommand ())
         {
            int a = 1;
            command.CommandText = "INSERT INTO 'SubBookOrder' (Name, OrderIndex) VALUES (@name, @index);";
            command.Parameters.AddWithValue("@name", "Name");
            command.Parameters.AddWithValue("@index", 0);
            foreach (var sb in subBookOrder)
            {
               command.Parameters [0].Value = sb;
               command.Parameters [1].Value = a;
               command.ExecuteNonQuery ();
               a++;
            }
            a = 1;
            command.CommandText = "INSERT INTO 'ChapterOrder' (Name, OrderIndex) VALUES (@name, @index);";
            foreach (var chap in chapterOrder)
            {
               command.Parameters [0].Value = chap;
               command.Parameters [1].Value = a;
               command.ExecuteNonQuery ();
               a++;
            }
            command.Parameters.Clear ();
            command.CommandText = "INSERT INTO 'BookInfo' (PropertyName, PropertyValue) VALUES (@pname, @pvalue);";
            command.Parameters.AddWithValue("@pname", "Name");
            command.Parameters.AddWithValue("@pvalue", title);
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "Author";
            command.Parameters [1].Value = author;
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "About";
            command.Parameters [1].Value = about;
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "Info";
            command.Parameters [1].Value = this.UnWiki (info);
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "Contents";
            command.Parameters [1].Value = contents;
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "HasSubBooks";
            command.Parameters [1].Value = hasSubBooks;
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "ChapterTitle";
            command.Parameters [1].Value = chapterTitle;
            command.ExecuteNonQuery ();
            command.Parameters [0].Value = "PageContent";
            command.Parameters [1].Value = content;
            command.ExecuteNonQuery ();
         }
      }

      /// <summary>
      /// Adds the given page data to the main database.
      /// </summary>
      /// <param name="title">Title of page.</param>
      /// <param name="content">Content of page</param>
      /// <param name="tableName">Name of table to add to.</param>
      /// <param name="unWiki">True to unwiki content, otherwise false.</param>
      private void AddToMain(string title, string content, string tableName, bool unWiki)
      {
         using (var command = this.m_DbMain.CreateCommand())
         {
            command.CommandText = string.Format("INSERT INTO '{0}' (Name, Content) VALUES (@name, @content);", tableName);
            command.Parameters.AddWithValue("@name", title);
            command.Parameters.AddWithValue("@content", unWiki ? this.UnWiki(content) : content);
            command.ExecuteNonQuery();
         }
      }

      /// <summary>
      /// Adds the given page data to the main database.
      /// </summary>
      /// <param name="title">Title of page.</param>
      /// <param name="content">Content of page</param>
      private void AddDefinition (string title, string content)
      {
         this.SetModifiedDate (Common.DEF_DB_NAME);
         using (var command = this.m_Dbs [Common.DEF_DB_NAME].CreateCommand())
         {
            command.CommandText = "INSERT INTO 'Definitions' (Name, Content) VALUES (@name, @content);";
            command.Parameters.AddWithValue("@name", title);
            command.Parameters.AddWithValue("@content", this.UnWiki(content));
            command.ExecuteNonQuery();
         }
      }
      
      /// <summary>
      /// Adds an excerpt to the proper database.
      /// </summary>
      /// <param name="book">Name of book.</param>
      /// <param name="subBook">Name of subbook.</param>
      /// <param name="chapter">Chapter title.</param>
      /// <param name="verse">Verse number.</param>
      /// <param name="passage">Excerpt to add.</param>
      /// <param name="mHead">Header.</param>
      /// <param name="mFoot">Footer</param>
      /// <param name="hasSubBooks">Does the database have sub books? Default is false.</param>
      private void AddExcerpt (string book, string subBook, string chapter, string verse, string passage,
                               Match mHead, Match mFoot, bool hasSubBooks = false)
      {
         var db = this.GetDb (book, hasSubBooks);
         using (var command = db.CreateCommand ())
         {
            command.CommandText = string.Format ("INSERT INTO 'Data' ({0}Chapter, Verse, Passage, " +
                                                 "Header, Footer) VALUES ({1}@chap, @verse, @pass, " +
                                                 "@head, @foot);", hasSubBooks ? "Book, " : string.Empty,
                                                 hasSubBooks ? "@book, " : string.Empty);
            if (hasSubBooks) command.Parameters.AddWithValue ("@book", subBook);
            command.Parameters.AddWithValue ("@chap", chapter);
            command.Parameters.AddWithValue ("@verse", verse);
            command.Parameters.AddWithValue ("@pass", this.UnWiki (passage));
            command.Parameters.AddWithValue ("@head", mHead.Success ? this.UnWiki (mHead.Groups [2].Value) : string.Empty);
            command.Parameters.AddWithValue ("@foot", mFoot.Success ? this.UnWiki (mFoot.Groups [2].Value) : string.Empty);
            command.ExecuteNonQuery ();
         }
      }
      
      /// <summary>
      /// Creates the main database file.
      /// </summary>
      private void CreateMainDb ()
      {
         this.m_DbMain = new SQLiteConnection (Sqlite.ConnectionString (this.m_ConvertDir + Common.MAIN_DB));
         this.m_DbMain.Open ();
         using (var command = this.m_DbMain.CreateCommand ())
         {
            command.CommandText = "CREATE TABLE 'Pages' (Name TEXT, Content TEXT);";
            command.ExecuteNonQuery ();
            command.CommandText = "CREATE TABLE 'Books' (Name TEXT, Database TEXT, Author TEXT, Date TEXT, About TEXT, LastModified TEXT);";
            command.ExecuteNonQuery ();
            command.CommandText = string.Format ("INSERT INTO 'Books' (Name, Database) VALUES ('{0}', '{1}');",
                                                 Common.DEF_DB_NAME, Common.DEF_DB);
            command.ExecuteNonQuery ();
         }
         this.m_Transactions.Add (this.m_DbMain.BeginTransaction ());
         
         var db = new SQLiteConnection (Sqlite.ConnectionString (this.m_ConvertDir + Common.DEF_DB));
         db.Open ();
         using (var command = db.CreateCommand ())
         {
            command.CommandText = "CREATE TABLE 'Definitions' (Name TEXT, Content TEXT);";
            command.ExecuteNonQuery ();
            command.CommandText = "CREATE TABLE 'BookInfo' (PropertyName TEXT, PropertyValue TEXT);";
            command.ExecuteNonQuery ();
         }
         this.m_Transactions.Add (db.BeginTransaction ());
         this.m_Dbs.Add (Common.DEF_DB_NAME, db);
         var listItem = new CheckedListItem { Name = Common.DEF_DB_NAME, Tag = Common.DEF_DB, IsChecked = true };
         MainViewModel.GuiDispatcher.Invoke (new Action <CheckedListItem> (item => this.ConvertedBooks.Add (item)), listItem);
      }
      
      /// <summary>
      /// Sets the last modified date for the given book.
      /// </summary>
      /// <param name="bookName">Name of book to set.</param>
      private void SetModifiedDate (string bookName)
      {
         if (!this.m_LastModifiedDates.ContainsKey (bookName))
            this.m_LastModifiedDates.Add (bookName, DateTime.Parse (this.m_TimeStamp));
         else
         {
            var timeStamp = this.m_LastModifiedDates [bookName];
            var currentTime = DateTime.Parse (this.m_TimeStamp);
            if (currentTime > timeStamp)
               this.m_LastModifiedDates [bookName] = currentTime;
         }
      }
      
      /// <summary>
      /// Gets the database for the given book name.
      /// </summary>
      /// <param name="bookName">Name of book to get database for.</param>
      /// <param name="hasSubBooks">True if the database has sub books.</param>
      /// <returns>The sqlite connection to the desired book.</returns>
      private SQLiteConnection GetDb (string bookName, bool hasSubBooks)
      {
         this.SetModifiedDate (bookName);
         if (this.m_Dbs.ContainsKey (bookName))
            return this.m_Dbs [bookName];
         string fileName = MakeValidFileName (bookName) + ".db";
         var conn = new SQLiteConnection (Sqlite.ConnectionString (this.m_ConvertDir + fileName));
         conn.Open ();
         using (var command = conn.CreateCommand ())
         {
            command.CommandText = "CREATE TABLE 'BookInfo' (PropertyName TEXT, PropertyValue TEXT);";
            command.ExecuteNonQuery ();
            command.CommandText = "CREATE TABLE 'SubBookOrder' (Name TEXT, OrderIndex INTEGER);";
            command.ExecuteNonQuery ();
            command.CommandText = "CREATE TABLE 'ChapterOrder' (Name TEXT, OrderIndex INTEGER);";
            command.ExecuteNonQuery ();
            if (hasSubBooks)
               command.CommandText = "CREATE TABLE 'Data' (Book VARCHAR (30), Chapter VARCHAR (30), " +
                  "Verse INTEGER, Passage TEXT, Header TEXT, Footer TEXT);";
            else
               command.CommandText = "CREATE TABLE 'Data' (Chapter VARCHAR (30), Verse INTEGER, Passage TEXT, Header TEXT, Footer TEXT);";
            command.ExecuteNonQuery ();
         }
         
         this.m_Transactions.Add (conn.BeginTransaction ());
         using (var command = this.m_DbMain.CreateCommand ())
         {
            command.CommandText = string.Format ("INSERT INTO 'Books' (Name, Database) VALUES ('{0}', '{1}');", bookName, fileName);
            command.ExecuteNonQuery ();
         }
         
         var listItem = new CheckedListItem { Name = bookName, Tag = fileName, IsChecked = true };
         MainViewModel.GuiDispatcher.Invoke (new Action <CheckedListItem> (item => this.ConvertedBooks.Add (item)), listItem);
         this.m_Dbs.Add (bookName, conn);
         return conn;
      }
      
      /// <summary>
      /// Makes the given file name valid.
      /// </summary>
      /// <param name="name">Orignial name of file.</param>
      /// <returns>Valid file name.</returns>
      private static string MakeValidFileName (string name)
      {
         string invalidChars = Regex.Escape (new string (Path.GetInvalidFileNameChars ()));
         string invalidReStr = string.Format (@"[{0}:]+", invalidChars);
         return Regex.Replace (name, invalidReStr, "_");
      }
      
      /// <summary>
      /// Removes the wiki formatting of the given text.
      /// </summary>
      /// <param name="text">Text to remove wiki formatting from.</param>
      /// <returns>Html text</returns>
      private string UnWiki (string text)
      {
         text = this.m_RegExDef.Replace (text, delegate (Match match)
            {
               return string.Format ("<abbr title=\"{1}\">{0}</abbr>",
                                     match.Groups [1].Value, match.Groups [2].Value);
            });
         text = this.m_RegExAmazonBook.Replace (text, delegate (Match match)
            {
               return string.Format ("<iframe src=\"http://rcm.amazon.com/e/cm?lt1=_blank&bc1=FFFFFF&IS1=1&bg1=FFFFFF&fc1=" +
                                        "000000&lc1=0000FF&t=spirsyne-20&o=1&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins={0}\" " +
                                        "style=\"width:120px;height:240px;\" scrolling=\"no\" marginwidth=\"0\" marginheight=\"0\" " +
                                        "frameborder=\"0\"></iframe>",
                                     match.Groups [1].Value);
            });
         return this.m_WikiParser.UnWiki (text).TrimEnd ('}');
      }
      
      /// <summary>
      /// Copies the selected converted books.
      /// </summary>
      private void CopyBooks ()
      {
         File.Copy (this.m_ConvertDir + Common.MAIN_DB, Common.DATA_DIR + Common.MAIN_DB, true);
         foreach (var book in this.ConvertedBooks)
         {
            if (book.IsChecked)
            {
               this.Status = "Copying " + book.Name;
               File.Copy (this.m_ConvertDir + book.Tag,
                          Common.DATA_DIR + book.Tag, true);
               book.IsChecked = false;
            }
         }
         this.Options.RefreshBooks ();
         this.Status = "Copy finished";
      }
      
      /// <summary>
      /// Checks to see if a command can execute.
      /// </summary>
      /// <param name="parameter">Command parameter.</param>
      /// <returns>True if able to execute, otherwise false.</returns>
      private bool CanDoAction (object parameter)
      {
         var param = parameter.ToString ();
         if (param == "Convert")
            return this.CanClose && !this.CanCopy;
         if (param == "Copy")
            return this.CanCopy;
         return true;
      }
      
      /// <summary>
      /// Executes a command.
      /// </summary>
      /// <param name="parameter">Command parameter.</param>
      private void OnAction (object parameter)
      {
         var param = parameter.ToString ();
         if (param == "Convert")
            this.Convert ();
         else if (param == "Copy")
            this.CopyBooks ();
      }
      
      /// <summary>
      /// Checks if ok can be clicked.
      /// </summary>
      /// <param name="parameter">Command parameter.</param>
      protected override bool CanOkExecute(object parameter)
      {
         return this.CanClose;
      }
      
      /// <summary>
      /// Occurs when ok/close is clicked.
      /// </summary>
      /// <param name="parameter">Command parameter.</param>
      protected override void OkExecuted(object parameter)
      {
         this.UnknownPages.Clear ();
         this.ConvertedBooks.Clear ();
         base.OkExecuted(parameter);
      }
      
      /// <summary>
      /// Disposes of any objects.
      /// </summary>
      public void Dispose()
      {
         this.m_DbMain.Close ();
         this.m_DbMain.Dispose ();
         foreach (var kvp in this.m_Dbs)
         {
            kvp.Value.Close ();
            kvp.Value.Dispose ();
         }
         this.m_Dbs.Clear ();
      }
      
      #endregion
   }
}
